package com.scs.application.modules.upgrade.db;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.scs.application.core.utils.StringUtils;
import com.scs.application.core.utils.UtilCommon;
import com.scs.application.core.utils.UtilNum;
import com.scs.application.modules.upgrade.db.annotation.EntityClassAnnotation;
import com.scs.application.modules.upgrade.db.annotation.EntityFieldAnnotation;
import com.scs.application.modules.upgrade.db.annotation.EntityIndexAnnotation;
import com.scs.application.modules.upgrade.db.enums.EntityFieldType;
import com.scs.application.modules.upgrade.db.enums.EntityIndexType;
import com.scs.application.modules.upgrade.db.obj.EntityClass;
import com.scs.application.modules.upgrade.db.obj.EntityField;
import com.scs.application.modules.upgrade.db.util.ClassReflect;
import com.scs.application.modules.upgrade.db.util.MySqlSysHelper;
import com.scs.application.modules.volant.io.ScanFile;
import com.scs.application.modules.volant.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;

import java.lang.reflect.Field;
import java.net.JarURLConnection;
import java.net.URL;
import java.util.*;
import java.util.jar.JarEntry;
import java.util.jar.JarFile;

@Service
@Slf4j
public class UpgradeDatabaseService {
    @Value("${upgrade-database.entity-packages}")
    private String entityPackages;
    @Value("${spring.profiles.active}")
    private String env;
    @Autowired
    private JdbcTemplate jdbcT;

    public void upgrade(String entityPaths) throws Exception {
        String classFile = "";
        ArrayList<EntityClass> entityClasses = new ArrayList<>();
        try {
            if (entityPaths.equals("")) {
                entityPaths = entityPackages;
            }
            String packageName = entityPaths.substring(0, entityPaths.indexOf(";") > -1 ? entityPaths.indexOf(";") : entityPaths.length());
            //通过当前线程得到类加载器从而得到URL的枚举
            Enumeration<URL> urlEnumeration = Thread.currentThread().getContextClassLoader().getResources(packageName.replace(".", "/"));
            while (urlEnumeration.hasMoreElements()) {
                URL url = urlEnumeration.nextElement();//得到的结果大概是：jar:file:/C:/Users/ibm/.m2/repository/junit/junit/4.12/junit-4.12.jar!/org/junit
                String protocol = url.getProtocol();//大概是jar

                if ("jar".equalsIgnoreCase(protocol)) {
                    //转换为JarURLConnection
                    JarURLConnection connection = (JarURLConnection) url.openConnection();
                    if (connection != null) {
                        JarFile jarFile = connection.getJarFile();
                        if (jarFile != null) {
                            //得到该jar文件下面的类实体
                            Enumeration<JarEntry> jarEntryEnumeration = jarFile.entries();
                            while (jarEntryEnumeration.hasMoreElements()) {
                                JarEntry entry = jarEntryEnumeration.nextElement();
                                String jarEntryName = entry.getName();
                                if (jarEntryName.contains(".class") && jarEntryName.replaceAll("/", ".").startsWith(packageName) && jarEntryName.contains("/entity/")) {
                                    classFile = jarEntryName.substring(0, jarEntryName.lastIndexOf(".")).replace("/", ".");
                                    addClassToUpdateTables(classFile,entityClasses);
                                }
                            }
                        }
                    }
                } else if ("file".equalsIgnoreCase(protocol)) {
                    // -- 扫描实体类 --
                    ArrayList<String> alClassFile = ScanFile.scanClass(entityPaths, true);
                    for (int i = 0; i < alClassFile.size(); i++) {
                        classFile = alClassFile.get(i);
                        addClassToUpdateTables(classFile,entityClasses);
                    }
                }
            }

            // -- 升级数据库 --
            int pageCurr=0,pagePer=20,pageSum= (int) Math.ceil(entityClasses.size()/pagePer);
            int maxNum = 0;


//            while(pageCurr < pageSum -1) {
//                maxNum = pagePer*(pageCurr+1);
//                if (maxNum > entityClasses.size()) maxNum = entityClasses.size();
//                log.info("UpgradeDatabaseService.upgrade.entityClassessize={} maxNum={} " + entityClasses.size(),maxNum);
//                List<EntityClass> entityClassSub = entityClasses.subList(pagePer*pageCurr,maxNum);
//
//                //刷新临时表数据
//                CompletableFuture.supplyAsync(() -> {
//                    Connection connection = null;
//                    try {
//                        connection = jdbcT.getDataSource().getConnection();
//                        connection.setAutoCommit(false);
//                        String sqlExe = "";
//                        for (EntityClass entityClass : entityClassSub) {
//                            log.info("UpgradeDatabaseService.upgrade.entityClass={} " + entityClass.name);
//                            sqlExe+=upgradeTable(entityClass);
//                        }
//                        if (StringUtils.isNotBlank(sqlExe)) jdbcT.execute(sqlExe);
//                        //需要操作数据库的两个insert，或者提供回调给业务开发人员
//                        connection.commit();
//                    } catch (Exception e) {
//                        log.error("UpgradeDatabaseService.upgrade.error ",e);
//                        try {
//                            connection.rollback();
//                        } catch (SQLException ex) {
//                            ex.printStackTrace();
//                        }
//                        System.exit(-1);
//                    }finally {
//                        try {
//                            if (connection != null) connection.setAutoCommit(false);
//                        } catch (SQLException e) {
//                        }
//                    }
//
//                    return "UpgradeDatabaseService.upgrade";
//                });
//                pageCurr++;
//            }

            String sqlExe = "";

            //所有表备注
            List<Map<String, Object>> tableList= null;
            Map<String,String> tableNameSet = new HashMap<String,String>();
            try {
                tableList= jdbcT.queryForList("select table_name,table_comment from information_schema.tables  where table_schema=(select database())  ");
            }catch (Exception e) {}
            if (tableList != null  && tableList.size() > 0)  tableList.stream().forEach(tmp ->tableNameSet.put(tmp.get("table_name")+"", tmp.get("table_comment")+""));


            for (EntityClass entityClass : entityClasses) {
                //log.info("upgrade database entityClass={} " + entityClass.name);
                sqlExe+= UtilCommon.getStr(upgradeTable(entityClass),"");

                if (StringUtils.isBlank(entityClass.remark)) continue;
                if (StringUtils.isBlank(tableNameSet.get(entityClass.name))
                 ||
                !tableNameSet.get(entityClass.name).equals(entityClass.remark)
                ) {
                    sqlExe+="ALTER TABLE "+entityClass.name+" COMMENT='" + entityClass.remark + "'; ";
                };
            }

            if (StringUtils.isNotBlank(sqlExe)) jdbcT.execute(sqlExe);

            //decimal 没有小数点的字段，改为4位小数点
            try {
                tableList= jdbcT.queryForList("select table_name,column_name,numeric_precision from INFORMATION_SCHEMA.Columns where  table_schema=(select database())   and data_type = 'decimal' and (NUMERIC_SCALE is null or NUMERIC_SCALE < 4) ");
            }catch (Exception e) {}
            if (tableList != null  && tableList.size() > 0) {
                tableList.stream().forEach(map -> {
                    int numeric_precision = Integer.valueOf(UtilCommon.getStr(map.get("numeric_precision")+"","0"));
                    if (numeric_precision <= 0) numeric_precision = 14;
                    if (numeric_precision <= 10) numeric_precision = 14;
                    jdbcT.execute("alter table "+map.get("table_name")+" modify column "+map.get("column_name")+" decimal(14, 4)");
                });
            };


            log.info("upgrade database success, total entity class: " + entityClasses.size());
        } catch (Exception e) {
            String message = e.getMessage();
            if (message == null) {
                throw e;
            }
            // 2020-04-02  yuntao.zhou 升级失败时提示
            if (message.indexOf("there can be only one auto column") != -1) {
                log.error("主键列当前为自增列，索引删除失败，请手工去除自动递增后重试");
            }
            else if (message.indexOf("Invalid use of NULL value") != -1) {
                log.error("当前修改的字段在数据中存在null值，请修改后重试");
            }
            throw e;
        }
    }

    private void addClassToUpdateTables(String className, ArrayList<EntityClass> entityClasses) {
        if (className.indexOf("Student") <= 0) {
            // continue;   // -- todo: 调试用 --
        }
        try {
            Class clazz = Thread.currentThread().getContextClassLoader().loadClass(className);
            if (clazz.getAnnotation(TableName.class) == null){ //没有TableName的注解
                return;
            }
            Object entity = clazz.newInstance();

            EntityClass entityClass = parseTable(entity);
            if (entityClass != null) {
                entityClasses.add(entityClass);
//                log.info("======== 实体类 ======== " + className);
            }
//            else {
//                log.info("====== 不是实体类 ====== " + className);
//            }
        } catch (InstantiationException e) {
            log.error("====== 不是普通类 ====== " + className,e);
        } catch (Exception e) {
            log.error("升级出错了：" + className,e);
        }
    }
    private EntityClass parseTable(Object entity) throws Exception {
        EntityClass entityClass = new EntityClass();

        // -- MyBatis注解分析 --
        TableName tableName = entity.getClass().getAnnotation(TableName.class);
        if (tableName != null) {
            entityClass.name = tableName.value();
        }
        // -- CPAS注解分析 --
        EntityClassAnnotation tableProperty = entity.getClass().getAnnotation(EntityClassAnnotation.class);
        if (tableProperty != null) {
            if (!tableProperty.name().equals("")) {
                entityClass.name = tableProperty.name();
            }
            if (!tableProperty.text().equals("")) {
                entityClass.text = tableProperty.text();
            }
            if (!tableProperty.remark().equals("")) {
                entityClass.remark = tableProperty.remark();
            }
        }
        if (entityClass.name.equals("")) {
            // -- 不是实体类 --
            return null;
        }
        EntityIndexAnnotation indexProperty = entity.getClass().getAnnotation(EntityIndexAnnotation.class);
        if (indexProperty != null) {
            entityClass.pk = indexProperty.pk();
            entityClass.ux = indexProperty.ux();
            entityClass.ix = indexProperty.ix();
        }

        // -- parse fields --
        List<Field> arrField = ClassReflect.getClassAllFields(entity);
        for (int i = 0; i < arrField.size(); i++) {
            EntityField entityField = parseField(entityClass, arrField.get(i));
            if (entityField != null) {
                entityClass.columnCount++;
                entityClass.addColumnDefinition(entityField);
                if (entityField.auto) {
                    entityClass.pk = entityField.name;
                }
            }
        }

        return entityClass;
    }
    private EntityField parseField(EntityClass entityClass, Field field) throws Exception {
        EntityField cd = new EntityField();
        TableField tableField = field.getAnnotation(TableField.class);
        if (tableField != null) {
            if (!tableField.exist()) {
                // -- 非数据库字段 --
                return null;
            }
            if (!tableField.value().equals("")) {
                cd.name = tableField.value();
            }
        }
        cd.name = field.getName();
        cd.type = cd.parseType(field.getType().getName());

        // -- MyBatis注解分析 --
        TableId tableId = field.getAnnotation(TableId.class);
        if (tableId != null) {
            if (!tableId.value().equals("")) {
                cd.name = tableId.value();
            }
            if (tableId.type() == IdType.AUTO || tableId.type() == IdType.ID_WORKER || tableId.type() == IdType.ID_WORKER_STR) {
                entityClass.pk = cd.name;
                cd.not_null = true;
            }
            if (tableId.type() == IdType.AUTO) {
                cd.auto = true;
            }
        }

        cd.name = StringUtil.humpToUnderline(cd.name);
        // -- CPAS注解分析 --
        EntityFieldAnnotation efAnnotation = field.getAnnotation(EntityFieldAnnotation.class);
        if (efAnnotation != null) {
            if (efAnnotation.auto()) {
                cd.auto = true;
            }
            if (efAnnotation.type() != EntityFieldType.UNKNOWN) {
                cd.type = efAnnotation.type();
            }
            if (!efAnnotation.default_value().equals("")) {
                cd.default_value = efAnnotation.default_value();
            }
            if (efAnnotation.not_null()) {
                cd.not_null = true;
            }
            cd.length = efAnnotation.length();
            cd.text = efAnnotation.text();
            cd.comment = efAnnotation.comment();
        }

        // -- 属性默认值 --
        if (cd.length.equals("")) {
            if (cd.type == EntityFieldType.STRING) {
                cd.length = "50";
            }
        }
        // -- end --
        return cd;
    }

    private String upgradeTable(EntityClass entityClass) throws Exception {
        String sql = "SELECT table_name FROM information_schema.TABLES WHERE table_schema=(select database()) and table_type = 'BASE TABLE' AND table_name = '" + entityClass.name + "'";
        SqlRowSet rsTable = jdbcT.queryForRowSet(sql);
        if (!rsTable.next()) {
            // -- 1. 表不存在：创建 --
            sql = entityClass.getCreateTableSql();
            jdbcT.execute(sql);
        }
        else {
            // -- 2. 表存在：比对字段、比对索引 --
            upgradeField(entityClass);
            return upgradeIndex(entityClass);
        }
        return null;
    }
    private void upgradeField(EntityClass entityClass) throws Exception {
        boolean blInvalidColumn;
        String columnName = "";
        String sql = "SELECT * FROM information_schema.COLUMNS WHERE table_schema=(select database()) and table_name ='" + entityClass.name + "'";
        String sqlExe = "";
        SqlRowSet rsColumn = jdbcT.queryForRowSet(sql);
        for (EntityField entityField : entityClass.entityFields) {
            boolean isNewColumn = true;
            rsColumn.beforeFirst();

            while ((rsColumn.next())) {
                if (rsColumn.getString("column_name").equalsIgnoreCase(entityField.name)) {
                    isNewColumn = false;
                    break;
                }
            }

            if (isNewColumn) {
                // -- 1. 列不存在，创建新列 --
                sql = "alter table " + entityClass.name + " add " + entityField.name + " " + entityField.getCreateColumnSql(true);
                log.info("新增字段：" + sql);
//                jdbcT.execute(sql);
                sqlExe+=sql +";";
            }
            else {
//                log.info("upgradeField.entityClass={}",entityClass.name);

                // -- 2. 列存在，比对列属性 --
                if (checkColumnNeedUpgrade(entityField, rsColumn.getString("data_type"),
                        rsColumn.getString("character_maximum_length"), rsColumn.getInt("numeric_precision"), rsColumn.getInt("numeric_scale"),
                        rsColumn.getString("is_nullable").equalsIgnoreCase("NO"), rsColumn.getString("column_default"), rsColumn.getString("column_comment"), rsColumn.getString("extra"))) {
                    sql = "alter table " + entityClass.name + " MODIFY COLUMN " + entityField.name + " " + entityField.getCreateColumnSql(false);
                    log.info("升级字段：" + sql);
//                    jdbcT.execute(sql);
                    sqlExe+=sql +";";
                }

            }
        }

        // -- 删除列 --
        rsColumn.beforeFirst();
        while (rsColumn.next()) {
            blInvalidColumn = true;
            columnName = rsColumn.getString("column_name");
            for (EntityField entityField : entityClass.entityFields) {
                if (entityField.name.equalsIgnoreCase(columnName)) {
                    blInvalidColumn = false;
                    break;
                }
            }
            if (blInvalidColumn) {
                if (env.equalsIgnoreCase("personal")) {
                    sqlExe+=MySqlSysHelper.getDropColumn(jdbcT, entityClass.name, columnName) +";";
                }
                // MySqlSysHelper.disableColumn(jdbcT, entityClass.name, columnName, rsColumn.getString("column_type"), rsColumn.getString("column_comment"));
            }
        }

        if (StringUtils.isNotBlank(sqlExe)) jdbcT.execute(sqlExe);

    }

    /**
     * 对比是否需要升级
     *
     * @return true：表示需要升级，false：表示不需要升级
     * @throws Exception 表示Column与Field的定义冲突，必须升级失败
     */
    private boolean checkColumnNeedUpgrade(EntityField entityField, String columnType, String columnLength, int columnPrecision, int columnScale, boolean columnNotNull, String columnDefault, String columnComment, String extra) throws Exception {
        boolean needUpgrade = true;
        String entityColumnType = entityField.getColumnType();
        // -- 0. 预处理 --
        if (columnDefault == null) {
            columnDefault = "";
        }
        else if (columnDefault.equals("")) {
            columnDefault = "''";
        }
        // -- 1. 判断是否有不同，有则需要升级 --
        for (int i = 0; i < 1; i++) {
            // -- 1.0 是否自增量不同 --
            if (entityField.auto != extra.equalsIgnoreCase("auto_increment")) {
                break;
            }
            if (!entityColumnType.equalsIgnoreCase(columnType)) {
                break;          // -- 1.1 数据类型不同 --
            }
            else {
                if (columnType.equals("float") || columnType.equals("double") || columnType.equals("decimal")) {
                    if (StringUtils.isNotBlank(entityField.length) && !(columnPrecision + "," + columnScale).equals(entityField.length)) {
                        break;  // -- 1.2 数字精度不同 --
                    }
                }
                else if (columnType.equals("varchar")) {
                    if (!columnLength.equals(entityField.length)) {
                        break;  // -- 1.3 字符长度不同 --
                    }
                }
            }

            if (columnNotNull != entityField.not_null) {
                break;          // -- 1.4 是否为空 --
            }
            if (!columnDefault.equals(entityField.default_value)) {
                boolean isDifferent = true;
                switch (columnType) { //精度的问题，0.00跟0.0000是一样的
                    case "decimal":
                    case "float":
                    case "double":
                        isDifferent = UtilNum.getDouble(columnDefault) != UtilNum.getDouble(entityField.default_value);
                        break;
                    default:
                        break;
                }
                if(isDifferent) {
                    break;          // -- 1.5 默认值不同 --
                }
            }

            // 备注：(字段名/中文名)|注释
            String commentNew = entityField.text.equals("") ? entityField.name : entityField.text
                                + (entityField.comment.equals("") ? "" : "|" + entityField.comment);
//            String commentNew = "";
//            if (!entityField.text.equals("") && !entityField.comment.equals("")) {
//                commentNew = entityField.text + "|" + entityField.comment;
//            }
//            else if (!entityField.text.equals("")) {
//                commentNew = entityField.text;
//            }
//            else if (!entityField.comment.equals("")) {
//                commentNew = entityField.name + "|" + entityField.comment;
//            }
            if (!columnComment.equals(commentNew)) {
                break;          // -- 1.6 备注不同 --
            }

            needUpgrade = false;
        }
        if (!needUpgrade) {
            return false;
        }
        // -- 2. 判断类型变化是否被允许 --
        if (!entityColumnType.equalsIgnoreCase(columnType)) {
            if ((columnType.equals("datetime") || columnType.equals("date") || columnType.equals("time"))
                    && (entityField.type != EntityFieldType.DATETIME && entityField.type != EntityFieldType.DATE && entityField.type != EntityFieldType.TIME)) {
                // -- 2.1 日期转为其它类型 --
                //log.info("entityField={}",entityField.name);
                throw new Exception("不允许从类型 " + columnType + " 转换为类型 " + entityField.type);
            }
            if ((entityField.type == EntityFieldType.DATETIME || entityField.type == EntityFieldType.DATE || entityField.type == EntityFieldType.TIME)
                    && (!columnType.equals("datetime") && !columnType.equals("date") && !columnType.equals("time"))) {
                // -- 2.2 其它转为日期类型 --
                //log.info("entityField={}",entityField.name);
                throw new Exception("不允许从类型 " + columnType + " 转换为类型 " + entityField.type);
            }

            // todo: 有数据情况尚未验证，存在数据，转换是否成功或损失精度？长度等等？
        }

        // -- 3. --

        return needUpgrade;
    }
    private String upgradeIndex(EntityClass entityClass) throws Exception {
        String sqlExe = "";

        String indexFields = "";
        ArrayList<String[]> alIndex;

        // -- 1. 主键 --
        indexFields = MySqlSysHelper.getPrimaryKey(jdbcT, entityClass.name);
        if (!entityClass.pk.equals(indexFields)) {
            if (!indexFields.equals("")) {
                MySqlSysHelper.dropIndex(jdbcT, entityClass.name, EntityIndexType.PRIMARY, "PRIMARY");
            }
            if (!entityClass.pk.equals("")) {
                MySqlSysHelper.addIndex(jdbcT, entityClass.name, EntityIndexType.PRIMARY, "PRIMARY", entityClass.pk);
            }
        }

        // -- 2. 唯一索引 --
        alIndex = MySqlSysHelper.getIndex(jdbcT, entityClass.name, EntityIndexType.UNIQUE_INDEX);
        for (int i = entityClass.ux.length; i < alIndex.size(); i++) {
            MySqlSysHelper.dropIndex(jdbcT, entityClass.name, EntityIndexType.UNIQUE_INDEX, alIndex.get(i)[0]);
        }
        for (int i = 0; i < entityClass.ux.length; i++) {
            if (i < alIndex.size()) {
                if (!entityClass.ux[i].equals(alIndex.get(i)[1])) {
                    MySqlSysHelper.dropIndex(jdbcT, entityClass.name, EntityIndexType.UNIQUE_INDEX, alIndex.get(i)[0]);
                    MySqlSysHelper.addIndex(jdbcT, entityClass.name, EntityIndexType.UNIQUE_INDEX, "UX__" + (i + 1), entityClass.ux[i]);
                }
            }
            else {
                MySqlSysHelper.addIndex(jdbcT, entityClass.name, EntityIndexType.UNIQUE_INDEX, "UX__" + (i + 1), entityClass.ux[i]);
            }
        }


        // -- 3. 普通索引 --
        //该表普通索引列表
        List<Map<String, Object>> tableIndexList= null;
        Map<String,String> indexNameSet = new HashMap<String,String>();
        Map<String,String> columnNameSet = new HashMap<String,String>();
        try {
            tableIndexList= jdbcT.queryForList("SELECT index_name,column_name FROM information_schema.statistics " +
                    " wHERE table_schema=(select database()) AND  NON_UNIQUE = 1  and table_name = '"+entityClass.name+"' ");
        }catch (Exception e) {}
        final String[] columnNameAll = {""};
        if (tableIndexList != null  && tableIndexList.size() > 0)  tableIndexList.stream().forEach(tmp ->{
            indexNameSet.put(tmp.get("index_name")+"", tmp.get("index_name")+"");
            columnNameSet.put(tmp.get("column_name")+"", tmp.get("column_name")+"");
            columnNameAll[0] +=","+tmp.get("column_name");
        });

        boolean isError = false;
        alIndex = MySqlSysHelper.getIndex(jdbcT, entityClass.name, EntityIndexType.INDEX);
        String sqlDropIndex = "",sqlAddIndex="";
//        for (int i = entityClass.ix.length; i < alIndex.size(); i++) {
//            sqlDropIndex+=MySqlSysHelper.getDropIndex(jdbcT, entityClass.name, EntityIndexType.INDEX, alIndex.get(i)[0])+";";
//        }
//        if (StringUtils.isNotBlank(sqlDropIndex)) jdbcT.execute(sqlDropIndex);

        sqlDropIndex="";
        for (int i = 0; i < entityClass.ix.length; i++) {
            try {
                String fieldName = entityClass.ix[i];
                String indexName = "IX__"+fieldName.replaceAll(",","_").replaceAll(" ","");
                //索引名称存在，则忽略
                if (indexNameSet.get(indexName) != null) {
                    continue;
                }


                //索引字段或者名称不存在，重建索引
                if (columnNameSet.get(fieldName) == null || indexNameSet.get(indexName) == null) {
                    isError = true;
                    sqlAddIndex ="";
                    break;
                }

                sqlAddIndex+=MySqlSysHelper.getAddIndex(jdbcT, entityClass.name, EntityIndexType.INDEX, indexName, entityClass.ix[i])+";";
            }catch ( Exception e) {
                e.printStackTrace();
                isError = true;
                break;
            }
        }

        if (StringUtils.isNotBlank(sqlDropIndex)) jdbcT.execute(sqlDropIndex);
        if (StringUtils.isNotBlank(sqlAddIndex)) jdbcT.execute(sqlAddIndex);


        //31 先删除所有普通索引，防止因为改变索引字段造成的索引重复
        if (isError) {
            String sql =" SELECT CONCAT('ALTER TABLE ', `Table`, ' DROP INDEX ', GROUP_CONCAT(`Index` SEPARATOR ', DROP INDEX '),';' ) ";
            sql+="  FROM ( ";
            sql+="      SELECT table_name AS `Table`, index_name AS `Index` FROM information_schema.statistics  ";
            sql+="      WHERE table_schema=(select database()) AND  NON_UNIQUE = 1  and table_name = '"+entityClass.name+"' GROUP BY `Table`, `Index`  ";
            sql+=" ) AS tmp GROUP BY `Table` ";
            String dropSql = null;

            try {
                dropSql = jdbcT.queryForObject(sql,String.class);
            }catch (Exception e) {
            }
            if (StringUtils.isNotBlank(dropSql)) sqlExe+=dropSql;

            for (int i = 0; i < entityClass.ix.length; i++) {
                String fieldName = entityClass.ix[i];
                String indexName = "IX__"+fieldName.replaceAll(",","_").replaceAll(" ","");
                sqlExe+=MySqlSysHelper.getAddIndex(jdbcT, entityClass.name, EntityIndexType.INDEX, indexName, entityClass.ix[i])+";";
            }
        }

        return sqlExe;
    }
}
